﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using SpLocalizer.Common;
using Microsoft.SharePoint;
using System.Xml;
using Microsoft.Practices.Unity;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Validation;
using DocumentFormat.OpenXml.Spreadsheet;


namespace SpLocalizer.ContentSourceProviders.SpDocumentLibraryFile
{
    internal class ExcelFileLocalizationTask: AbstractFileLocalizationTask
    {

         /// <summary>
        /// Constructor without parameters should be public for unity injection
        /// </summary>
        public ExcelFileLocalizationTask () { }

        private const string SlideXmlElementName = "Part";

        protected override string GetDocumentXmlToTranslate(SPListItem item)
        {            
            // Open a PresentationDocument for read-only access based on a stream.
            using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(item.File.OpenBinaryStream(), false))
            {
                XmlDocument XmlDoc = new XmlDocument();
                XmlDoc.LoadXml(@"<CustomExcelDocument></CustomExcelDocument>");
                XmlNode Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "strings", "");
                Xnode.InnerXml = excelDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.InnerXml;
                XmlDoc.DocumentElement.AppendChild(Xnode);


                foreach (WorksheetPart part in excelDocument.WorkbookPart.WorksheetParts)
                {
                    if (part.DrawingsPart != null)
                    {
                        foreach (ChartPart partx in part.DrawingsPart.ChartParts)
                        {
                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "charts", "");
                            Xnode.InnerXml = partx.ChartSpace.InnerXml;
                            XmlDoc.DocumentElement.AppendChild(Xnode);
                        }
                    }
                   /* if (part.PivotTableParts != null)
                    {
                        foreach (PivotTablePart partx in part.PivotTableParts)
                        {
                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "pivottables", "");
                            Xnode.InnerXml = partx.PivotTableDefinition.InnerXml;
                            XmlDoc.DocumentElement.AppendChild(Xnode);
                        }
                    } */
                    if (part.TableDefinitionParts != null)
                    {
                        foreach (TableDefinitionPart partx in part.TableDefinitionParts)
                        {
                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "tables", "");
                            Xnode.InnerXml = partx.Table.InnerXml;
                            XmlDoc.DocumentElement.AppendChild(Xnode);
                        }
                    }
                }
                //


                Sheets sheet = excelDocument.WorkbookPart.Workbook.Sheets;

                foreach (Sheet sheett in sheet)
                {
                    OpenXmlPart mypart =  excelDocument.WorkbookPart.GetPartById(sheett.Id);
                    if (Convert.ToString(mypart.Uri).IndexOf("/xl/worksheets") != -1)
                    {
                        WorksheetPart worksheetPart = (WorksheetPart)excelDocument.WorkbookPart.GetPartById(sheett.Id);
                        Worksheet worksheet = worksheetPart.Worksheet;
                        //sheett.InnerXml
                        Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "sheets", "");
                        Xnode.InnerXml = worksheet.InnerXml;
                        XmlDoc.DocumentElement.AppendChild(Xnode);
                    }
                }


                //


                if (XmlDoc.DocumentElement.ChildNodes.Count == 0)
                    return null;
                
                using (StringWriter sw = new StringWriter()){
                using(XmlTextWriter xtw = new XmlTextWriter(sw)){    
                    XmlDoc.WriteContentTo(xtw);
                    xtw.Flush();
                    sw.Flush();
                    return sw.ToString();
                }
                }
            }
            
        }

        internal override void ExportTranslation(SPListItem item)
        {

            if (!Constants.FILE_EXCEL.Equals(this.Format))
            {
                throw new ApplicationException(String.Format("Illegal format {0} for export with {1}. {2} Required",
                    this.Format, typeof(ExcelFileLocalizationTask).Name, Constants.FILE_EXCEL));
            }
                SPFile theFile = item.File;


                // Open a WordprocessingDocument for read-only access based on a stream.
                Stream documentStream = theFile.OpenBinaryStream();
                using (SpreadsheetDocument xlsxDocument = SpreadsheetDocument.Open(documentStream, true))
                {

                    XmlDocument XmlDoc = new XmlDocument();
                    XmlDoc.LoadXml(this.TranslatedText);
                    int i = 0;
                    //




 
                    XmlNode Xnode = XmlDoc.SelectNodes("//*[local-name()='strings']").Item(0);
                    xlsxDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.InnerXml = Xnode.InnerXml;


                    foreach (WorksheetPart part in xlsxDocument.WorkbookPart.WorksheetParts)
                    {
                        if (part.DrawingsPart != null)
                        {
                            i = 0;
                            foreach (ChartPart partx in part.DrawingsPart.ChartParts)
                            {
                                Xnode = XmlDoc.SelectNodes("//*[local-name()='charts']").Item(i);
                                partx.ChartSpace.InnerXml = Xnode.InnerXml;
                                i++;
                            }
                        }
                       /* if (part.PivotTableParts != null)
                        {
                            i = 0;
                            foreach (PivotTablePart partx in part.PivotTableParts)
                            {
                                Xnode = XmlDoc.SelectNodes("//*[local-name()='pivottables']").Item(i);
                                partx.PivotTableDefinition.InnerXml = Xnode.InnerXml;
                                i++;
                            }
                        }*/

                        if (part.TableDefinitionParts != null)
                        {
                            i = 0;
                            foreach (TableDefinitionPart partx in part.TableDefinitionParts)
                            {
                                Xnode = XmlDoc.SelectNodes("//*[local-name()='tables']").Item(i);
                                partx.Table.InnerXml = Xnode.InnerXml;
                                i++;
                            }
                        }
                    }
                    //




                    i = 0;

                     foreach (Sheet sheett in xlsxDocument.WorkbookPart.Workbook.Sheets)
                    {
                                             OpenXmlPart mypart =  xlsxDocument.WorkbookPart.GetPartById(sheett.Id);
                                             if (Convert.ToString(mypart.Uri).IndexOf("/xl/worksheets") != -1)
                                             {
                                                 WorksheetPart worksheetPart = (WorksheetPart)xlsxDocument.WorkbookPart.GetPartById(sheett.Id);
                                                 Worksheet worksheet = worksheetPart.Worksheet;
                                                 Xnode = XmlDoc.SelectNodes("//*[local-name()='sheets']").Item(i);
                                                 worksheet.InnerXml = Xnode.InnerXml;
                                                 i++;
                                             }
                    }
                    

                    //









                    //

                    xlsxDocument.Close();

                    SafeFile(theFile, documentStream);

                }
            

        }
    }
}


